package org.king.util;


import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.atomic.AtomicBoolean;

/**
 * 数据库类，单例，外接直接通过下面方式调用
 * 通过Database.getInstance().getConnection();
 */
public class Database {

    private static final AtomicBoolean initialized = new AtomicBoolean(false);
    private static final Database instance = new Database() ;
    private final HikariDataSource dataSource;


    private static final String URL = "jdbc:mysql://localhost:3306/db";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    // 私有构造方法，防止外部直接实例化
    private Database() {
        this.dataSource = initializeDataSource();
        // 保证初始化只执行一次
        if (initialized.compareAndSet(false, true)) {
            initializeDatabase();
        }
    }

    // 线程安全
    public static Database getInstance() {
        return instance;
    }

    private HikariDataSource initializeDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        // 连接池优化配置
        config.setMaximumPoolSize(10);       // 最大连接数
        config.setMinimumIdle(5);            // 最小空闲连接
        config.setConnectionTimeout(30000);   // 连接超时时间(毫秒)
        config.setIdleTimeout(600000);       // 空闲连接超时时间(毫秒)
        config.setMaxLifetime(1800000);      // 连接最大生命周期(毫秒)
        config.setPoolName("DatabasePool"); // 连接池名称

        // MySQL优化
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("useServerPrepStmts", "true");

        return new HikariDataSource(config);
    }
    /**
     * 获取数据库连接
     * @return Connection 数据库连接对象
     * @throws SQLException 如果获取连接失败
     */
    public Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    private synchronized void initializeDatabase(){
        try(Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            createTables(conn);
            System.out.println("Tables created.");
        }catch(SQLException e){
            System.out.println("Tables could not be created.");
            e.printStackTrace();
        }
    }

    private void createTables(Connection conn) throws SQLException {
        try(Statement sta = conn.createStatement()){
            String sql1 = "CREATE TABLE IF NOT EXISTS users ("+
                    "id INT PRIMARY KEY AUTO_INCREMENT, " +
                    "account char(10) not null unique, " +
                    "password varchar(255) not null, " +
                    "email varchar(30)," +
                    "role varchar(30));";
            sta.executeUpdate(sql1);

            String sql2 = "create table if not exists activities (" +
                    "activity_id INT PRIMARY KEY AUTO_INCREMENT, " +
                    "name varchar(50) not null, " +
                    "activity_describe varchar(255) ," +
                    "begin_time datetime not null," +
                    "end_time datetime not null," +
                    "address varchar(255) not null," +
                    "create_id int," +
                    "foreign key(create_id) references users(id));";
            sta.executeUpdate(sql2);

            String sql3 = "create table if not exists user_join_activity(" +
                    "user_id int, " +
                    "activity_id int, " +
                    "score int not null default 0, " +
                    "primary key (user_id, activity_id)," +
                    "foreign key (user_id) references users(id)," +
                    "foreign key (activity_id) references activities(activity_id)," +
                    "constraint check_score check(score between 0 and 10));";
            sta.executeUpdate(sql3);

            String sql4 = "create table if not exists comment(" +
                    "comment_id INT PRIMARY KEY AUTO_INCREMENT," +
                    "user_id int," +
                    "activity_id int," +
                    "text varchar(255) not null," +
                    "foreign key (user_id) references users(id)," +
                    "foreign key (activity_id) references activities(activity_id)); ";
            sta.executeUpdate(sql4);
        }
    }

    // 关闭数据源（应用退出时调用）
    public synchronized void shutdown() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
            initialized.set(false);
        }
    }

    //测试数据库
    public static void main(String[] args) {
        // 通过单例模式获取实例
        Database database = Database.getInstance();
        // 执行其他操作，譬如获取连接
        try (Connection conn = database.getConnection()) {
            System.out.println("成功连接到数据库！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
